package org.fage.semples.dao;

import java.util.ArrayList;
import java.util.List;

import org.fage.semples.model.ExamSubject;
import org.fage.semples.model.PagerModel;
import org.fage.semples.util.SystemContext;
import org.hibernate.type.StringType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository
@Transactional
public class ExamSubjectHiberanteDAOImpI extends BaseHibernateDAO<ExamSubject> implements ExamSubjectHiberanteDAO{

	@Autowired
	private ExamSubjectHiberanteDAO examSubjectHiberanteDAO;
	
	@SuppressWarnings("unchecked")
	public PagerModel<ExamSubject> findAllByPage() {
		PagerModel<ExamSubject> page = new PagerModel<ExamSubject>();
		ArrayList<ExamSubject> dates = new ArrayList<ExamSubject> ();
		int offset = SystemContext.getOffset();
		int pageSize = SystemContext.getSize();
		//使用hibernate完成分页
		List<ExamSubject> tempDates = this.getSession().createQuery(" from ExamSubject ").setFirstResult(offset).setMaxResults(pageSize).list();
		for(ExamSubject examSubject : tempDates){
			dates.add(examSubject);
		}
		page.setDates(dates);
		page.setOffset(offset);
		page.setPageSize(pageSize);
		
		//获取总记录数
		long totalSize = (long)this.getSession().createQuery("select count(*) from ExamSubject").uniqueResult();
		page.setTotalSize(totalSize);
		
		return page;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<String> getAllUserBySubjcet(String subId) {
		Integer subjectId = Integer.parseInt(subId);
		StringBuilder sql = new StringBuilder();
		sql.append(" select user.user_id as userId ");
		sql.append(" from exam_user_subject eus ");
		sql.append(" left join exam_subject es on eus.sub_id = es.sub_id ");
		sql.append(" left join exam_user user on user.user_id = eus.user_id ");
		sql.append(" where es.sub_id = :subjectId ");
		return this.createSqlQuery(String.valueOf(sql))
				.addScalar("userId", StringType.INSTANCE)
				.setInteger("subjectId", subjectId)
				.list();
	}
	
	@Override
	public ExamSubject getExamSubjectById(String subId) {
		return this.get(subId);
	}

	@Override
	public List<ExamSubject> findAllExamSubject() {
		return this.find("FROM ExamSubject");
	}

	@Override
	public void createExamSubject(ExamSubject examSubject) throws Exception {
		this.save(examSubject);
	}

	@Override
	public void editExamSubject(ExamSubject examSubject) throws Exception {
		this.update(examSubject);
	}

	@Override
	public void deleteExamSubject(String subId) throws Exception {
		
		this.deleteByKey(subId);
	}

	@Override
	public void saveSubjectStudent(List<String> subjectStudents, Integer subjectId) {
		List<String> dbSubjectUserlist = examSubjectHiberanteDAO.getAllUserBySubjcet(subjectId.toString());
		List<String> insertList = new ArrayList<String>();
		List<String> delList = new ArrayList<String>();
		
		for(String selectedUser : subjectStudents){
			if(!dbSubjectUserlist.contains(selectedUser)){
				insertList.add(selectedUser);
			}
		}
		
		for(String selectedUser : dbSubjectUserlist){
			if(!subjectStudents.contains(selectedUser)){
				delList.add(selectedUser);
			}
		}
		
		examSubjectHiberanteDAO.insertSubjectUser(insertList, subjectId);
		examSubjectHiberanteDAO.deleteSubjectUser(delList, subjectId);
	}

	@Override
	public void insertSubjectUser(List<String> insertList, Integer subjectId) {
		String sql = null;
		for(String userId:insertList){
			sql = "insert into exam_user_subject(user_id,sub_id) values(:userId, :subjectId)";
			this.createSqlQuery(sql).setString("userId", userId).setInteger("subjectId", subjectId).executeUpdate();
		}
	}

	@Override
	public void deleteSubjectUser(List<String> delList, Integer subjectId) {
		String sql = null;
		for(String userId:delList){
			sql = "delete exam_user_subject from exam_user_subject where user_id = :userId and sub_id = :subjectId";
			this.createSqlQuery(sql).setString("userId", userId).setInteger("subjectId", subjectId).executeUpdate();
		}
	}
	
}
